// AgriDao.js
class AgriDao {
  constructor(pool) {
    this.pool = pool; // 将连接池保存到实例的 this.pool
  }
    // 确保图片数量至少5张
    ensureFiveImages(images) {
      while (images.length < 5) {
        images.push("http://159.75.133.177:8080/images/fruit.png");
      }
      return images;
    }



  // 根据ID获取产品信息
  async getProductById(product_id) {
    const productSql = `
      SELECT ap.*, u.user_id, u.nick_name, u.role_name AS role, u.user_phone, u.user_email 
      FROM agri_products ap
      JOIN users u ON ap.user_id = u.user_id
      WHERE ap.product_id = ?`;

    const imageSql = `SELECT image_url FROM product_images WHERE product_id = ?`;
    
    try {
      const [productRows] = await this.pool.query(productSql, [product_id]);
      if (productRows.length === 0) return null;
      
      const product = productRows[0];
      product.seller = {
        user_id: product.user_id,
        user_name: product.nick_name,
        role: product.role,
        phone: product.user_phone,
        email: product.user_email,
      };
      delete product.user_id;
      delete product.nick_name;
      delete product.role;
      delete product.user_phone;
      delete product.user_email;
      
      const [imageRows] = await this.pool.query(imageSql, [product_id]);
      product.images = this.ensureFiveImages(imageRows.map(row => row.image_url));
      
      return product;
    } catch (error) {
      throw new Error('查询产品信息失败: ' + error.message);
    }
  }

  // 获取用户的所有农产品（包括已下架的）
  async getUserProducts(userId) {
    const productSql = `
      SELECT ap.*, u.user_id, u.nick_name, u.role_name AS role,  u.user_phone, u.user_email 
      FROM agri_products ap
      JOIN users u ON ap.user_id = u.user_id
      WHERE ap.user_id = ?`;

    const imageSql = `SELECT image_url FROM product_images WHERE product_id = ?`;
    
    try {
      const [productRows] = await this.pool.query(productSql, [userId]);
      const products = [];

      for (const row of productRows) {
        row.seller = {
          user_id: row.user_id,
          user_name: row.nick_name,
          role: row.role,
          phone: row.user_phone,
          email: row.user_email,
        };
        delete row.user_id;
        delete row.nick_name;
        delete row.role;
        delete row.user_phone;
        delete row.user_email;

        const [imageRows] = await this.pool.query(imageSql, [row.product_id]);
        row.images = this.ensureFiveImages(imageRows.map(image => image.image_url));
        
        products.push(row);
      }
      return products;
    } catch (error) {
      throw new Error('查询用户的农产品失败: ' + error.message);
    }
  }

  // 软删除（下架）农产品
  async deactivateProduct(product_id, userId) {
      const [result] = await this.pool.query(
          'UPDATE agri_products SET status = "inactive" WHERE product_id = ? AND user_id = ? AND status != "inactive"',
          [product_id, userId]
      );
      return result; // 返回更新操作的结果
  }

  // 恢复（上架）农产品
  async reactivateProduct(product_id, userId) {
      const [result] = await this.pool.query(
          'UPDATE agri_products SET status = "active" WHERE product_id = ? AND user_id = ? AND status = "inactive"',
          [product_id, userId]
      );
      return result; // 返回更新操作的结果
  }
  // 获取政府发布的农产品
  async getGovernmentProducts() {
    const sql = `
        SELECT ap.*, u.user_id, u.nick_name, u.role_name AS role, u.user_phone, u.user_email 
        FROM agri_products ap
        JOIN users u ON ap.user_id = u.user_id
        WHERE u.role_name = 'gov' AND ap.status = 'active'`;

    const imageSql = `SELECT image_url FROM product_images WHERE product_id = ?`;

    try {
      const [productRows] = await this.pool.execute(sql);
      const products = [];

      for (const row of productRows) {


        row.seller = {
          user_id: row.user_id,
          user_name: row.nick_name,
          role: row.role,
          phone: row.user_phone,
          email: row.user_email,
        };
        delete row.user_id;
        delete row.nick_name;
        delete row.role;
        delete row.user_phone;
        delete row.user_email;
        const [imageRows] = await this.pool.execute(imageSql, [row.product_id]);
        row.images = this.ensureFiveImages(imageRows.map(image => image.image_url));



        products.push(row);
      }

      return products;
    } catch (error) {
      throw new Error("查询政府发布的农产品失败: " + error.message);
    }
  }

  // 获取农户发布的农产品
  async getFarmerProducts() {
    const sql = `
        SELECT ap.*, u.user_id, u.nick_name, u.role_name AS role, u.user_phone, u.user_email 
        FROM agri_products ap
        JOIN users u ON ap.user_id = u.user_id
        WHERE u.role_name = 'farmer' AND status != "inactive"`;

    const imageSql = `SELECT image_url FROM product_images WHERE product_id = ?`;

    try {
      const [productRows] = await this.pool.execute(sql);
      const products = [];

      for (const row of productRows) {

        row.seller = {
          user_id: row.user_id,
          user_name: row.nick_name,
          role: row.role,
          phone: row.user_phone,
          email: row.user_email,
        };
        delete row.user_id;
        delete row.nick_name;
        delete row.role;
        delete row.user_phone;
        delete row.user_email;
        const [imageRows] = await this.pool.execute(imageSql, [row.product_id]);
        row.images = this.ensureFiveImages(imageRows.map(image => image.image_url));
        products.push(row);
      }

      return products;
    } catch (error) {
      throw new Error("查询农户发布的农产品失败: " + error.message);
    }
  }

  // 根据产品名字和角色筛选公告
  async searchProductAnnouncements(title, role) {
    const sql = `
        SELECT 
            ap.*, u.user_id, u.nick_name, u.role_name AS role, u.user_phone, u.user_email ,
            GROUP_CONCAT(ui.image_url) AS images 
        FROM agri_products ap
        JOIN users u ON ap.user_id = u.user_id
        LEFT JOIN product_images ui ON ap.product_id = ui.product_id
        WHERE LOWER(ap.product_name) LIKE LOWER(?) 
        AND u.role_name = ?
        AND ap.status = 'active'
        GROUP BY ap.product_id`;

    try {
      const [rows] = await this.pool.execute(sql, [`%${title}%`, role]);

      return rows.map(row => {
        const seller = {
            user_id: row.user_id,
            user_name: row.nick_name,
            role: row.role,
            phone: row.user_phone,
            email: row.user_email,
        };

        // 创建一个新的对象，不包含seller的信息
        const newRow = {
            ...row,
        };

        // 删除新对象中不需要的字段
        delete newRow.user_id;
        delete newRow.nick_name;
        delete newRow.role;
        delete newRow.user_phone;
        delete newRow.user_email;

        return {
            ...newRow,
            seller: seller,
            images: this.ensureFiveImages(row.images ? row.images.split(",") : []),
        };
      });

    } catch (error) {
      throw new Error("查询公告失败: " + error.message);
    }
  }

  
  
  

  // 获取产品库存
  async getProductStock(product_id) {
    const sql = `SELECT listed_quantity FROM agri_products WHERE product_id = ?`;
    const [rows] = await this.pool.execute(sql, [product_id]); // 使用 this.pool
    if (rows.length === 0) throw new Error('产品不存在');
    return rows[0].stock;
  }

  // 减少库存
  async reduceStock(product_id, quantity) {
    const sql = `UPDATE agri_products SET listed_quantity = listed_quantity - ? WHERE product_id = ? AND listed_quantity >= ?`;
    const [result] = await this.pool.execute(sql, [quantity, product_id, quantity]); // 使用 this.pool
    if (result.affectedRows === 0) throw new Error('库存不足');
  }
  async createAgriProduct(product_name, category, region, price, total_stock, listed_quantity, description, user_id,introduction) {
    const sql = `
      INSERT INTO agri_products (
        product_name, category, region, price, total_stock, 
        listed_quantity, description, created_at, status ,user_id ,introduction
      ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), 'active' ,? ,?)
    `;
    try {
      const [result] = await this.pool.execute(sql, [
        product_name, category, region, price, total_stock,
        listed_quantity, description, user_id, introduction
      ]);
      return result;
    } catch (error) {
      console.error('Error creating agri product:', error);
      throw new Error('创建农产品时发生错误: ' + error.message);
    }
  }


  // 插入产品图片
  async insertProductImage(product_id, image_url) {
    const sql = `
      INSERT INTO product_images (product_id, image_url)
      VALUES (?, ?)
    `;
    try {
      const [result] = await this.pool.execute(sql, [product_id, image_url]);
      return result;
    } catch (error) {
      console.error('Error inserting product image:', error);
      throw new Error('插入产品图片时发生错误: ' + error.message);
    }
  }
}

module.exports = AgriDao;
